#!/usr/bin/env python
# -*- coding: utf-8 -*-
# 时间     : 2019-12-23 17:20:18
# 作者     : DL (584054432@qq.com)
# 网址     : https://gitee.com/dingliangPython/
# 软件版本 : Python3.7.3
# 功能     ：

# import openpyxl
from openpyxl import load_workbook,utils
import tools
def read(file,beginRow=1):
    xlsx = load_workbook(file, data_only=True)
    # 打开excel
    sheetNames = xlsx.sheetnames
    # 获取所有表名称
    sheetData = {}
    for sheetName in sheetNames:
        sheet = xlsx[sheetName]
        # 选择一个表
        rows = len(list(xlsx[sheetName].rows))  # 行
        if rows >= 1:  # 判断是否为空表
            # 列名
            columnNames = [utils.get_column_letter(x) for x in range(1, sheet.max_column + 1)]
            # 工作薄数据
            rowDatas = [ [sheet["{}{}".format(i,k)].value for i in columnNames] for k in range(beginRow, sheet.max_row + 1) if sheet["A{}".format(k)].value ]
            # 存储到dict
            sheetData.setdefault(sheetName, rowDatas)
    xlsx.close()
    return sheetData
def write(file,data,sheetName=None,row=None,column=None):
    """摘要
    
    功能描述：
            写所有工作薄，data={'工作薄名'：[ [1行1列值,...],[2行1列值,...] ]}
            写指定工作薄，data=[ [1行1列值,...],[2行1列值,...]]
            写指定行值 data=[值1，值2...]
            写指定单元格值 data=xxx
    
    参数:
        file: [描述: 文件路径]
        data: [描述: 数据]
        sheetName: [默认参数描述: 指定工作薄名] (默认: {None})
        row: [默认参数描述: 指定行] (默认: {None})
        column: [默认参数描述: 指定列,数字配合row,/单元格：A1] (默认: {None})
    
    返回:
        [返回值描述:True/异常]
    """
    try:
        xlsx = load_workbook(file)
        if not sheetName:
            for name,value in data.items():
                print(name,value)
                if name not in xlsx.sheetnames: xlsx.create_sheet(name)
                sheet=xlsx[name]
                for i,row in enumerate(value):
                    for ii,v in enumerate(row):
                        sheet.cell(row=i+1,column=ii+1,value=v)
        elif sheetName and not row and not column:
            sheet=xlsx[sheetName]
            for i,row in enumerate(data):
                for ii,v in enumerate(row):
                    sheet.cell(row=i+1,column=ii+1,value=v)
        elif sheetName and row and not column:
            sheet=xlsx[sheetName]
            for ii,v in enumerate(row):
                sheet.cell(row=row,column=ii+1,value=v)
        elif sheetName and column:
            sheet=xlsx[sheetName]
            if str(column).isdigit():
                sheet.cell(row=row,column=column,value=data)
            else:
                sheet[column.upper()].value=data
        xlsx.save(file)
        xlsx.close()
        return True
    except Exception as e:
        tools.Set_Message("文件写入异常，请检测文件是否已打开，如已打开请关闭重新写入，异常：{},\n{}".format(e,write.__doc__))

if __name__ == '__main__':
    print(write('test.xlsx','测ddd试','Sheet1',row=2,column=4))